package com.ctshk.rpc.order.goods.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.common.dto.Result;
import com.ctshk.rpc.goods.CommoditySeasonDTO;
import com.ctshk.rpc.goods.dto.ShoppingSpecialCommodityDTO;
import com.ctshk.rpc.goods.dto.htmlFive.CommodityAllListDTO;
import com.ctshk.rpc.order.goods.dto.CommodityDTO;
import com.ctshk.rpc.order.goods.dto.CommodityInfoListDTO;
import com.ctshk.rpc.order.goods.dto.CommodityPageDTO;
import com.ctshk.rpc.order.goods.dto.CommoditySpecialDTO;
import com.ctshk.rpc.order.goods.dto.resp.CommodityPageReq;
import com.ctshk.rpc.order.goods.dto.resp.CountVo;
import com.ctshk.rpc.order.goods.entity.Commodity;
import com.ctshk.rpc.order.goods.mapper.provider.CommodityProvider;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 商品表 Mapper 接口
 * </p>
 *
 * @author 姜斌文
 * @since 2021-04-13
 */
public interface CommodityMapper extends BaseMapper<Commodity> {

    @SelectProvider(type = CommodityProvider.class, method = "findCommodityPageByCondition")
    IPage<CommodityPageDTO> findCommodityPageByCondition(IPage<CommodityPageDTO> page, CommodityPageReq pageReq);

    @Select("SELECT t.id,t.commodity_classification_id,t.commodity_classification_name,t.commodity_name,t.supplier_id,t.supplier_name,t.charge_person_id,t.charge_person_name,t.commodity_brand_id,t.commodity_brand_name,t.commodity_introduce,t.unit_of_measurement_id,t.unit_of_measurement_name,t.sort,t.pack_status,t.freight_template_id,t.template_name,t.Is_it_self_provided,t.after_sale_service,t.refund_service_charge_type,t.refund_service_charge,t.matters_needing_attention,t.commodity_details,t.commodity_number,t.sale_status,t.approval_status,t.approval_id,t.gmt_create,t.create_id,t.gmt_modified,t.modified_id,t.is_deleted\n" +
            ", attr.id AS \"commodityAttributeReq.id\", attr.commodity_id AS \"commodityAttributeReq.commodity_id\", attr.specifications_type AS \"commodityAttributeReq.specifications_type\", attr.distribution_commission_settings AS \"commodityAttributeReq.distribution_commission_settings\", attr.currency_id AS \"commodityAttributeReq.currency_id\", attr.currency_name AS \"commodityAttributeReq.currency_name\", attr.exchange_rate AS \"commodityAttributeReq.exchange_rate\"\n" +
            ", sa.id AS \"commoditySalesAreaReq.id\", sa.commodity_id AS \"commoditySalesAreaReq.commodity_id\", sa.area_type AS \"commoditySalesAreaReq.area_type\", sa.commodity_attribute_id AS \"commoditySalesAreaReq.commodity_attribute_id\"\n" +
            " FROM `ctshk-goods`.t_commodity t \n" +
            "LEFT JOIN `ctshk-goods`.t_commodity_attribute attr ON attr.commodity_id = t.id\n" +
            "LEFT JOIN `ctshk-goods`.t_commodity_sales_area sa ON sa.commodity_id = t.id\n" +
            "WHERE t.id = #{id}")
    CommodityDTO findCommodityByCommodityId(@Param("id") Long id);

    @Select("SELECT t.id,t.commodity_classification_id,t.commodity_classification_name,t.commodity_name,t.supplier_id,t.supplier_name,t.charge_person_id,t.charge_person_name,t.commodity_brand_id,t.commodity_brand_name,t.commodity_introduce,t.unit_of_measurement_id,t.unit_of_measurement_name,t.sort,t.pack_status,t.freight_template_id,t.template_name,t.Is_it_self_provided,t.after_sale_service,t.refund_service_charge_type,t.refund_service_charge,t.matters_needing_attention,t.commodity_details,t.commodity_number,t.sale_status,t.approval_status,t.approval_id,t.gmt_create,t.create_id,t.gmt_modified,t.modified_id,t.is_deleted\n" +
            ", attr.id AS \"commodityAttributeReq.id\", attr.commodity_id AS \"commodityAttributeReq.commodity_id\", attr.specifications_type AS \"commodityAttributeReq.specifications_type\", attr.distribution_commission_settings AS \"commodityAttributeReq.distribution_commission_settings\", attr.currency_id AS \"commodityAttributeReq.currency_id\", attr.currency_name AS \"commodityAttributeReq.currency_name\", attr.exchange_rate AS \"commodityAttributeReq.exchange_rate\"\n" +
            ", sa.id AS \"commoditySalesAreaReq.id\", sa.commodity_id AS \"commoditySalesAreaReq.commodity_id\", sa.area_type AS \"commoditySalesAreaReq.area_type\", sa.commodity_attribute_id AS \"commoditySalesAreaReq.commodity_attribute_id\"\n" +
            " FROM `ctshk-goods`.t_commodity t \n" +
            "LEFT JOIN `ctshk-goods`.t_commodity_attribute attr ON attr.commodity_id = t.id\n" +
            "LEFT JOIN `ctshk-goods`.t_commodity_sales_area sa ON sa.commodity_id = t.id\n" +
            "WHERE t.id = #{id} AND t.gmt_modified >= #{gmtModified}")
    CommodityDTO findPcCommodityByCommodityId(@Param("id") Long id,@Param("gmtModified")LocalDateTime now);

    /**
     * 根据商品主键ID查询商品信息列表（用于商城专题，查询详情时获取商品信息）
     * @param commodityIds 商品主键id，如:1382521857446809602, 1382521857446809603, 1382521857446809604
     * @return 集合
     */
    @Select("<script>" +
            "SELECT  vv.id, vv.commodity_name,vv.file_url, vv.price, vv.stock,vv.specifications_type,vv.attributeId " +
            "FROM (SELECT t.id, t.commodity_name,cab.specifications_type,cab.id as attributeId, " +
            "(SELECT GROUP_CONCAT(file.file_url) FROM `ctshk-goods`.t_commodity_file file WHERE file.commodity_id = t.id and file.file_type = 2)  as  file_url, " +
            "(SELECT MIN(cost.price) FROM `ctshk-goods`.t_commodity_cost cost WHERE cost.commodity_id = t.id and cost.commodity_attribute_id = cab.id)  as  price, " +
            "(SELECT SUM(cost.stock) FROM `ctshk-goods`.t_commodity_cost cost WHERE cost.commodity_id = t.id and cost.commodity_attribute_id = cab.id)  as  stock " +
            "FROM `ctshk-goods`.t_commodity t LEFT JOIN `ctshk-goods`.t_commodity_attribute cab " +
            "ON ( cab.commodity_id = t.id ) " +
            "WHERE  t.is_deleted = 0 and t.approval_status = 3) vv " +
            "WHERE vv.id IN (#{commodityIds})" +
            "</script>")
    List<ShoppingSpecialCommodityDTO> findShoppingSpecialCommodityByIds(@Param("commodityIds") String commodityIds);

    @Select("<script>" +
            "SELECT  vv.id, vv.commodity_name,vv.file_url, vv.price, vv.stock,vv.specifications_type,vv.attributeId " +
            "FROM (SELECT t.id, t.commodity_name,cab.specifications_type,cab.id as attributeId, " +
            "(SELECT GROUP_CONCAT(file.file_url) FROM `ctshk-goods`.t_commodity_file file WHERE file.commodity_id = t.id and file.file_type = 2)  as  file_url, " +
            "(SELECT MIN(cost.price) FROM `ctshk-goods`.t_commodity_cost cost WHERE cost.commodity_id = t.id and cost.commodity_attribute_id = cab.id)  as  price, " +
            "(SELECT SUM(cost.stock) FROM `ctshk-goods`.t_commodity_cost cost WHERE cost.commodity_id = t.id and cost.commodity_attribute_id = cab.id)  as  stock " +
            "FROM `ctshk-goods`.t_commodity t LEFT JOIN `ctshk-goods`.t_commodity_attribute cab " +
            "ON ( cab.commodity_id = t.id ) " +
            "WHERE  t.is_deleted = 0 and t.approval_status = 3) vv " +
            "WHERE vv.id IN (${commodityIds})" +
            "</script>")
    List<com.ctshk.rpc.order.goods.dto.ShoppingSpecialCommodityDTO> findAppShoppingSpecialCommodityByIds(@Param("commodityIds") String commodityIds);

    // =============================== H5端 Start ===============================
    @Select("SELECT t.id,t.commodity_classification_id,t.commodity_classification_name,t.commodity_name,t.supplier_id,t.supplier_name,t.charge_person_id,t.charge_person_name,t.commodity_brand_id,t.commodity_brand_name,t.commodity_introduce,t.unit_of_measurement_id,t.unit_of_measurement_name,t.sort,t.pack_status,t.freight_template_id,t.template_name,t.Is_it_self_provided,t.after_sale_service,t.refund_service_charge_type,t.refund_service_charge,t.matters_needing_attention,t.commodity_details,t.commodity_number,t.sale_status,t.approval_status,t.approval_id,t.gmt_create,t.create_id,t.gmt_modified,t.modified_id,t.is_deleted\n" +
            ", attr.id AS \"commodityAttributeReq.id\", attr.commodity_id AS \"commodityAttributeReq.commodity_id\", attr.specifications_type AS \"commodityAttributeReq.specifications_type\", attr.distribution_commission_settings AS \"commodityAttributeReq.distribution_commission_settings\", attr.currency_id AS \"commodityAttributeReq.currency_id\", attr.currency_name AS \"commodityAttributeReq.currency_name\", attr.exchange_rate AS \"commodityAttributeReq.exchange_rate\"\n" +
            ", sa.id AS \"commoditySalesAreaReq.id\", sa.commodity_id AS \"commoditySalesAreaReq.commodity_id\", sa.area_type AS \"commoditySalesAreaReq.area_type\", sa.commodity_attribute_id AS \"commoditySalesAreaReq.commodity_attribute_id\"\n" +
            " FROM `ctshk-goods`.t_commodity t \n" +
            "LEFT JOIN `ctshk-goods`.t_commodity_attribute attr ON attr.commodity_id = t.id\n" +
            "LEFT JOIN `ctshk-goods`.t_commodity_sales_area sa ON sa.commodity_id = t.id\n" +
            "WHERE t.id = #{id}")
    CommodityDTO findHtmlFiveCommodityByCommodityId(@Param("id") Long id);


    @Select({
            "<script>",
            "SELECT",
            "t.id,t.commodity_classification_id,t.commodity_classification_name,t.commodity_name,t.supplier_id,t.supplier_name,t.charge_person_id,t.charge_person_name,t.commodity_brand_id,t.commodity_brand_name,t.commodity_introduce,t.unit_of_measurement_id,t.unit_of_measurement_name,t.sort,t.pack_status,t.freight_template_id,t.template_name,t.Is_it_self_provided,t.after_sale_service,t.refund_service_charge_type,t.refund_service_charge,t.matters_needing_attention,t.commodity_details,t.commodity_number,t.sale_status,t.approval_status,t.approval_id,t.gmt_create,t.create_id,t.gmt_modified,t.modified_id,t.is_deleted ",
            ",att.id as 'commodityAttribute.id',att.commodity_id as 'commodityAttribute.commodityId',att.specifications_type as 'commodityAttribute.specificationsType',att.distribution_commission_settings as 'commodityAttribute.distributionCommissionSettings',att.currency_id as 'commodityAttribute.currencyId',att.currency_name as 'commodityAttribute.currencyName',att.exchange_rate as 'commodityAttribute.exchangeRate',att.gmt_create as 'commodityAttribute.gmtCreate',att.create_id as 'commodityAttribute.createId',att.gmt_modified as 'commodityAttribute.gmtModified',att.modified_id as 'commodityAttribute.modifiedId',att.is_deleted as 'isDeleted' ",
            ",csa.id as 'commoditySalesArea.id',csa.commodity_id as 'commoditySalesArea.commodityId',csa.area_type as 'commoditySalesArea.areaType',csa.commodity_attribute_id as 'commoditySalesArea.commodityAttributeId',csa.gmt_create as 'commoditySalesArea.gmtCreate',csa.create_id as 'commoditySalesArea.createId',csa.gmt_modified as 'commoditySalesArea.gmtModified',csa.modified_id as 'commoditySalesArea.modifiedId',csa.is_deleted as 'isDeleted' ",
            "FROM `ctshk-goods`.t_commodity t ",
            "LEFT JOIN `ctshk-goods`.t_commodity_attribute att ON (att.commodity_id = t.id) ",
            "LEFT JOIN `ctshk-goods`.t_commodity_sales_area csa ON (csa.commodity_id = t.id AND csa.commodity_attribute_id = att.id)",
            "WHERE t.sale_status = 1 AND t.is_deleted = 0 AND t.approval_status = 3 ",
            "<if test='lastNow != null'> AND t.gmt_modified >= #{lastNow}</if> ",
            "ORDER BY t.sort ASC, t.gmt_modified DESC ",
            "</script>"
    })
    List<CommodityAllListDTO> findCommodityAllList(@Param("lastNow") LocalDateTime lastNow);


    @Select({
            "<script>",
            "SELECT",
            "t.id,t.commodity_name,t.sort,tcc.cost_price,att.currency_id,att.currency_name,tcf.file_url " +
                    "FROM `ctshk-goods`.t_commodity t ",
            "LEFT JOIN `ctshk-goods`.t_commodity_attribute att ON (att.commodity_id = t.id) ",
            "LEFT JOIN `ctshk-goods`.t_commodity_cost tcc ON (tcc.commodity_id = t.id) ",
            "LEFT JOIN `ctshk-goods`.t_commodity_file tcf ON (tcf.commodity_id = t.id) ",
            "LEFT JOIN `ctshk-goods`.t_commodity_sales_area csa ON (csa.commodity_id = t.id AND csa.commodity_attribute_id = att.id)",
            "WHERE t.sale_status = 1 " +
                    "AND t.is_deleted = 0 " + // 未删除
                    "AND t.approval_status = 3 " + // 审批通过
                    "AND att.specifications_type = 1", // 单规格
                    "AND tcf.file_type = 2", // 图片封面
            "<if test='keyword != null'> AND t.commodity_name like concat('%',#{keyword},'%') </if> ",
            "ORDER BY t.sort ASC, t.gmt_modified DESC ",
            "</script>"
    })
    List<CommoditySpecialDTO> findCommodityAllListBykeyword(@Param("keyword") String keyword);

    // =============================== H5端 End ===============================


    @Select({
            "<script>",
            "SELECT",
            "t.id,t.commodity_classification_id,t.commodity_classification_name,t.commodity_name,t.supplier_id,t.supplier_name,t.charge_person_id,t.charge_person_name,t.commodity_brand_id,t.commodity_brand_name,t.commodity_introduce,t.unit_of_measurement_id,t.unit_of_measurement_name,t.sort,t.pack_status,t.freight_template_id,t.template_name,t.Is_it_self_provided,t.after_sale_service,t.refund_service_charge_type,t.refund_service_charge,t.matters_needing_attention,t.commodity_details,t.commodity_number,t.sale_status,t.approval_status,t.approval_id,t.gmt_create,t.create_id,t.gmt_modified,t.modified_id,t.is_deleted ",
            ",att.id as 'commodityAttribute.id',att.commodity_id as 'commodityAttribute.commodityId',att.specifications_type as 'commodityAttribute.specificationsType',att.distribution_commission_settings as 'commodityAttribute.distributionCommissionSettings',att.currency_id as 'commodityAttribute.currencyId',att.currency_name as 'commodityAttribute.currencyName',att.exchange_rate as 'commodityAttribute.exchangeRate',att.gmt_create as 'commodityAttribute.gmtCreate',att.create_id as 'commodityAttribute.createId',att.gmt_modified as 'commodityAttribute.gmtModified',att.modified_id as 'commodityAttribute.modifiedId',att.is_deleted as 'isDeleted' ",
            ",csa.id as 'commoditySalesArea.id',csa.commodity_id as 'commoditySalesArea.commodityId',csa.area_type as 'commoditySalesArea.areaType',csa.commodity_attribute_id as 'commoditySalesArea.commodityAttributeId',csa.gmt_create as 'commoditySalesArea.gmtCreate',csa.create_id as 'commoditySalesArea.createId',csa.gmt_modified as 'commoditySalesArea.gmtModified',csa.modified_id as 'commoditySalesArea.modifiedId',csa.is_deleted as 'isDeleted' ",
            "FROM `ctshk-goods`.t_commodity t ",
            "LEFT JOIN `ctshk-goods`.t_commodity_attribute att ON (att.commodity_id = t.id) ",
            "LEFT JOIN `ctshk-goods`.t_commodity_sales_area csa ON (csa.commodity_id = t.id AND csa.commodity_attribute_id = att.id)",
            "WHERE t.sale_status = 1 AND t.is_deleted = 0 AND t.approval_status = 3 ",
            "<if test='lastNow != null'> AND t.gmt_modified >= #{lastNow}</if> ",
            "GROUP BY t.id " +
            "ORDER BY t.sort ASC, t.gmt_modified DESC ",
            "</script>"
    })
    List<CommodityInfoListDTO> findCommodityInfoList(@Param("lastNow") LocalDateTime lastNow);

    @Select({
            "<script>",
            "SELECT",
            "t.id,t.commodity_classification_id,t.commodity_classification_name,t.commodity_name,t.supplier_id,t.supplier_name,t.charge_person_id,t.charge_person_name,t.commodity_brand_id,t.commodity_brand_name,t.commodity_introduce,t.unit_of_measurement_id,t.unit_of_measurement_name,t.sort,t.pack_status,t.freight_template_id,t.template_name,t.Is_it_self_provided,t.after_sale_service,t.refund_service_charge_type,t.refund_service_charge,t.matters_needing_attention,t.commodity_details,t.commodity_number,t.sale_status,t.approval_status,t.approval_id,t.gmt_create,t.create_id,t.gmt_modified,t.modified_id,t.is_deleted ",
            ",att.id as 'commodityAttribute.id',att.commodity_id as 'commodityAttribute.commodityId',att.specifications_type as 'commodityAttribute.specificationsType',att.distribution_commission_settings as 'commodityAttribute.distributionCommissionSettings',att.currency_id as 'commodityAttribute.currencyId',att.currency_name as 'commodityAttribute.currencyName',att.exchange_rate as 'commodityAttribute.exchangeRate',att.gmt_create as 'commodityAttribute.gmtCreate',att.create_id as 'commodityAttribute.createId',att.gmt_modified as 'commodityAttribute.gmtModified',att.modified_id as 'commodityAttribute.modifiedId',att.is_deleted as 'isDeleted' ",
            ",csa.id as 'commoditySalesArea.id',csa.commodity_id as 'commoditySalesArea.commodityId',csa.area_type as 'commoditySalesArea.areaType',csa.commodity_attribute_id as 'commoditySalesArea.commodityAttributeId',csa.gmt_create as 'commoditySalesArea.gmtCreate',csa.create_id as 'commoditySalesArea.createId',csa.gmt_modified as 'commoditySalesArea.gmtModified',csa.modified_id as 'commoditySalesArea.modifiedId',csa.is_deleted as 'isDeleted' ",
            "FROM `ctshk-goods`.t_commodity t ",
            "LEFT JOIN `ctshk-goods`.t_commodity_attribute att ON (att.commodity_id = t.id) ",
            "LEFT JOIN `ctshk-goods`.t_commodity_sales_area csa ON (csa.commodity_id = t.id AND csa.commodity_attribute_id = att.id)",
            "WHERE t.sale_status = 1 AND t.is_deleted = 0 AND t.approval_status = 3 ",
            "<if test='keyword != null'> AND t.commodity_name like concat('%',#{keyword},'%') </if> ",
            "<if test='lastNow != null'> AND t.gmt_modified >= #{lastNow}</if> ",
            "GROUP BY t.id " +
                    "ORDER BY t.sort ASC, t.gmt_modified DESC ",
            "</script>"
    })
    List<CommodityInfoListDTO> findCommodityInfoListByKeyWord(@Param("keyword") String keyword,@Param("lastNow") LocalDateTime lastNow);

    /**
     * 查询七天内商品销售数量
     * @return
     */
    @Select({
            "<script>",
            "SELECT count(c.commodity_id) as count, c.commodity_id as commodityId FROM `ctshk-goods-order`.t_order o LEFT JOIN `ctshk-goods-order`.t_order_commodity c ON o.id = c.order_id WHERE DATE_SUB( CURDATE(), INTERVAL 40 DAY ) &lt;= DATE( o.gmt_create ) GROUP BY c.commodity_id ",
            "</script>"
    })
    List<CountVo> findSavenDaysCommodityNumber();


    /**
     * 获取商品成本价
     * @param commodityId 商品id
     * @param currentDateTime 当天时间
     * @return
     */
    @Select({
            "<script>" +
                    "SELECT c.*,cc.stock,cc.price,cca.specifications_attribute_one_name,specifications_attribute_one_id,specifications_attribute_two_id,specifications_attribute_two_name\n" +
                    "FROM `ctshk-goods`.t_commodity c\n" +
                    "LEFT JOIN `ctshk-goods`.t_commodity_cost cc\n" +
                    "ON c.id = cc.commodity_id\n" +
                    "LEFT JOIN `ctshk-goods`.t_commodity_cost_attribute cca\n" +
                    "ON cca.commodity_id = c.id\n" +
                    "<where>" +
                    "<if test='specificationsAttributeOneId!=null'> AND cca.specifications_attribute_one_id = #{specificationsAttributeOneId}</if>" +
                    "<if test='specificationsAttributeTwoId!=null'> AND cca.specifications_attribute_two_id = #{specificationsAttributeTwoId}</if>" +
                    "AND cca.commodity_id = #{commodityId}" +
                    "AND cca.is_deleted = 0" +
                    "AND cc.is_deleted = 0 " +
                    "AND c.is_deleted = 0" +
                    "AND TO_DAYS(cc.gmt_modified) = #{currentDateTime}" +
                    "</where>" +
                    "</script>"
    })
    Result<CommoditySeasonDTO> findCommodityPriceById(@Param("commodityId") Long commodityId, @Param("currentDateTime") LocalDate currentDateTime, @Param("specificationsAttributeOneId") Long specificationsAttributeOneId, @Param("specificationsAttributeTwoId") Long specificationsAttributeTwoId);

    /**
     * 根据商品id查询商品详情
     * @param id
     * @return
     */
    @Select({
            "SELECT * FROM `ctshk-goods`.t_commodity WHERE id = #{id}"
    })
    Commodity selectCommodityById(@Param("id") String id);
}
